ALTER                    proc sp_get_valid_wafers_for_lots
@assembly_release_id int,
@lot_id int

as
begin

declare @mainCursor 	CURSOR
declare @subCursor	CURSOR
declare @wafer_id 	varchar(50)
declare @bin_1		int
declare @bin_2		int
declare @bin_3		int
declare @bin_4		int
declare @bin_5		int
declare @bin_6		int
declare @bin_7		int
declare @bin_8		int
declare @bin_9		int
declare @bin_10		int
declare @bin_11		int
declare @bin_12		int
declare @bin_13		int
declare @bin_14		int
declare @bin_15		int
declare @wafer_saved    int
declare @wafer_id_temp  varchar(50)
declare @bin_total	bigint
declare @wafer_exists   int

declare @results Table ( wafer_id varchar(50), bin_1 int, bin_2 int, bin_3 int, bin_4 int, bin_5 int, bin_6 int, bin_7 int, bin_8 int, bin_9 int, bin_10 int, bin_11 int, bin_12 int, bin_13 int, bin_14 int, bin_15 int, bin_total bigint )

	if( @assembly_release_id > 0 )
	begin
		
		set @mainCursor = CURSOR FOR select wafer_id, bin_1, bin_2, bin_3, bin_4, bin_5, bin_6, bin_7, bin_8, bin_9, bin_10, bin_11, bin_12, bin_13, bin_14, bin_15, ( bin_1 + bin_2 + bin_3 + bin_4 + bin_5 + bin_6 + bin_7 + bin_8 + bin_9 + bin_10 + bin_11 + bin_12 + bin_13 + bin_14 + bin_15 )  from informative_binning NOLOCK where lot_id = @lot_id
		open @mainCursor 
		fetch next from @mainCursor into @wafer_id, @bin_1, @bin_2, @bin_3, @bin_4, @bin_5, @bin_6, @bin_7, @bin_8, @bin_9, @bin_10, @bin_11, @bin_12, @bin_13, @bin_14, @bin_15, @bin_total 
		while @@fetch_status = 0
		begin
			select @wafer_saved = count( lot_id ) from Assembly_Release_Bin_Information NOLOCK where lot_id = @lot_id and wafer_id = @wafer_id
			if( @wafer_saved = 0 ) 
			begin
				insert into @results values ( @wafer_id, @bin_1, @bin_2, @bin_3, @bin_4, @bin_5, @bin_6, @bin_7, @bin_8, @bin_9, @bin_10, @bin_11, @bin_12, @bin_13, @bin_14, @bin_15, @bin_total ) 
			end
		
		fetch next from @mainCursor into @wafer_id, @bin_1, @bin_2, @bin_3, @bin_4, @bin_5, @bin_6, @bin_7, @bin_8, @bin_9, @bin_10, @bin_11, @bin_12, @bin_13, @bin_14, @bin_15, @bin_total  
		end
		close @mainCursor
		deallocate @mainCursor	
		
		set @subCursor = CURSOR for select wafer_id from assembly_release_bin_information where assemblyrelease_id = @assembly_release_id 
		open @subCursor	fetch next from @subCursor into @wafer_id_temp
		while @@fetch_status = 0
		begin
			select @wafer_exists = count( wafer_id ) from @results where wafer_id = @wafer_id_temp
			if( @wafer_exists = 0 )		
			begin
				insert into @results Select wafer_id, bin_1, bin_2, bin_3, bin_4, bin_5, bin_6, bin_7, bin_8, bin_9, bin_10, bin_11, bin_12, bin_13, bin_14, bin_15, bin_1 + bin_2 + bin_3 + bin_4 + bin_5 + bin_6 + bin_7 + bin_8 + bin_9 + bin_10 + bin_11 + bin_12 + bin_13 + bin_14 + bin_15 bin_total from informative_binning where lot_id = @lot_id and wafer_id = @wafer_id_temp
			end
		fetch next from @subCursor into @wafer_id_temp
		end
		close @subCursor
		deallocate @subCursor

	end
	else
	begin
		set @mainCursor = CURSOR FOR select wafer_id, bin_1, bin_2, bin_3, bin_4, bin_5, bin_6, bin_7, bin_8, bin_9, bin_10, bin_11, bin_12, bin_13, bin_14, bin_15, ( bin_1 + bin_2 + bin_3 + bin_4 + bin_5 + bin_6 + bin_7 + bin_8 + bin_9 + bin_10 + bin_11 + bin_12 + bin_13 + bin_14 + bin_15 )  from informative_binning NOLOCK where lot_id = @lot_id
		open @mainCursor 
		fetch next from @mainCursor into @wafer_id, @bin_1, @bin_2, @bin_3, @bin_4, @bin_5, @bin_6, @bin_7, @bin_8, @bin_9, @bin_10, @bin_11, @bin_12, @bin_13, @bin_14, @bin_15, @bin_total 
		while @@fetch_status = 0
		begin
		
			select @wafer_saved = count( lot_id ) from Assembly_Release_Bin_Information NOLOCK where lot_id = @lot_id and wafer_id = @wafer_id
			if( @wafer_saved = 0 ) 
			begin
				insert into @results values ( @wafer_id, @bin_1, @bin_2, @bin_3, @bin_4, @bin_5, @bin_6, @bin_7, @bin_8, @bin_9, @bin_10, @bin_11, @bin_12, @bin_13, @bin_14, @bin_15, @bin_total ) 
			end
		
		
		fetch next from @mainCursor into @wafer_id, @bin_1, @bin_2, @bin_3, @bin_4, @bin_5, @bin_6, @bin_7, @bin_8, @bin_9, @bin_10, @bin_11, @bin_12, @bin_13, @bin_14, @bin_15, @bin_total  
		end
		close @mainCursor
		deallocate @mainCursor
	end

select * from @results order by wafer_id

return
end